package com.dy.yunying.biz.dao.znfx;

import com.dy.yunying.api.req.znfx.AdPlanAnalyseReq;
import com.dy.yunying.api.resp.znfx.AdPlanAnalyseRes;
import com.dy.yunying.api.resp.znfx.AdPlanReportRes;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.RequiredArgsConstructor;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;

@Log4j2
@Component
@RequiredArgsConstructor
public class AdPlanAnalyseDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	private final YunYingProperties prop;

	/**
	 * 计划属性
	 * @param record
	 * @return
	 */
	public List<AdPlanAnalyseRes> adPlanData(AdPlanAnalyseReq record){
		final String sql = this.getPlanAttr(record);
		log.debug("智能分析-计划属性列表SQL: [\n{}]", sql);

		long start = System.currentTimeMillis();
		final List<AdPlanAnalyseRes> list = clickhouseTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(AdPlanAnalyseRes.class));
		long end = System.currentTimeMillis();
		log.info("智能分析-计划属性列表SQL: {}ms", end - start);
		return list;
	}

	public List<AdPlanReportRes> adPlanCollect(AdPlanAnalyseReq record){
		final String sql = this.getPlanReportCollect(record);
		log.debug("智能分析-计划报表汇总列表SQL: [\n{}]", sql);

		long start = System.currentTimeMillis();
		final List<AdPlanReportRes> list = clickhouseTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(AdPlanReportRes.class));
		long end = System.currentTimeMillis();
		log.info("智能分析-计划报表汇总SQL: {}ms", end - start);
		return list;
	}

	private String getPlanAttr(AdPlanAnalyseReq record){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT  ").append("\n");
		sql.append("        adid AS adId, ").append("\n");
		sql.append(" 		delivery_range AS deliveryRange, -- 投放范围 ").append("\n");
		sql.append(" 		budget_mode AS budgetMode, -- 预算类型 ").append("\n");
		sql.append(" 		download_type AS downloadType, --下载方式 ").append("\n");
		sql.append(" 		external_action AS externalAction, -- 转化目标 ").append("\n");
		sql.append(" 		deep_external_action AS deepExternalAction, -- 深度转化目标 ").append("\n");
		sql.append(" 		gender AS gender, -- 性别 ").append("\n");
		sql.append(" 		age AS age, -- 年龄 ").append("\n");
		sql.append(" 		interest_action_mode AS interestActionMode, -- 行为兴趣 ").append("\n");
		sql.append(" 		device_type AS deviceType, -- 设备类型 ").append("\n");
		sql.append(" 		ac AS ac, -- 受众网络类型 ").append("\n");
		sql.append(" 		carrier AS carrier, -- 运营商 ").append("\n");
		sql.append(" 		hide_if_converted AS hideIfConverted, -- 过滤已转化用户 ").append("\n");
		sql.append(" 		smart_bid_type AS smartBidType, -- 出价方式 ").append("\n");
		sql.append(" 		adjust_cpa AS adjustCpa, -- 调整自动出价 ").append("\n");
		sql.append(" 		schedule_type AS scheduleType, -- 投放时间 ").append("\n");
		sql.append(" 		deep_bid_type AS deepBidType, -- 深度优化方式 ").append("\n");
		sql.append(" 		education AS education -- 学历 ").append("\n");
		sql.append(" FROM ( ").append("\n");
		sql.append("		").append(this.getPlanReportData(record)).append("\n");
		sql.append(" ) a  ").append("\n");
		sql.append(" LEFT JOIN ( ").append("\n");
		sql.append(" 		SELECT  ").append("\n");
		sql.append(" 				ad_id AS adid , -- 计划ID ").append("\n");
		sql.append(" 				advertiser_id AS advertiser_id, -- 广告账户 ").append("\n");
		sql.append(" 				ctype AS ctype, -- 平台ID ").append("\n");
		sql.append(" 				delivery_range AS delivery_range, -- 投放范围 ").append("\n");
		sql.append(" 				budget_mode AS budget_mode, -- 预算类型 ").append("\n");
		sql.append(" 				download_type AS download_type, --下载方式 ").append("\n");
		sql.append(" 				external_action AS external_action, -- 转化目标 ").append("\n");
		sql.append(" 				deep_external_action AS deep_external_action, -- 深度转化目标 ").append("\n");
		sql.append(" 				gender AS gender, -- 性别 ").append("\n");
		sql.append(" 				age AS age, -- 年龄 ").append("\n");
		sql.append(" 				interest_action_mode AS interest_action_mode, -- 行为兴趣 ").append("\n");
		sql.append(" 				device_type AS device_type, -- 设备类型 ").append("\n");
		sql.append(" 				ac AS ac, -- 受众网络类型 ").append("\n");
		sql.append(" 				carrier AS carrier, -- 运营商 ").append("\n");
		sql.append(" 				hide_if_converted AS hide_if_converted, -- 过滤已转化用户 ").append("\n");
		sql.append(" 				smart_bid_type AS smart_bid_type, -- 出价方式 ").append("\n");
		sql.append(" 				adjust_cpa AS adjust_cpa, -- 调整自动出价 ").append("\n");
		sql.append(" 				schedule_type AS schedule_type, -- 投放时间 ").append("\n");
		sql.append(" 				deep_bid_type AS deep_bid_type, -- 深度优化方式 ").append("\n");
		sql.append(" 				education AS education -- 学历 ").append("\n");
		sql.append(" 		FROM v_ad_plan_analyse ").append("\n");
		sql.append(" 		WHERE 1=1 ").append("\n");
		if (StringUtils.isNotBlank(record.getPlatformId())){
			sql.append(" 		AND ctype IN (").append(record.getPlatformId()).append(")\n");
		}
		if (StringUtils.isNotBlank(record.getAdAccount())){
			sql.append(" AND advertiser_id IN ('").append(record.getAdAccount().replaceAll(",", "','")).append("') \n");
		}
		if (StringUtils.isNotBlank(record.getAdId())){
			sql.append(" AND ad_id IN ('").append(record.getAdId().replaceAll(",", "','")).append("') \n");
		}
		sql.append(" ) b ON a.adid = toString(b.adid) ").append("\n");
		return sql.toString();
	}

	private String getPlanReportCollect(AdPlanAnalyseReq record){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT  ").append("\n");
		sql.append(" 	round(SUM(rudeCost),2) rudeCost,--原始消耗 ").append("\n");
		sql.append(" 	round(SUM(cost),2) totalCost,--返点后消耗 ").append("\n");
		sql.append(" 	SUM(adIdNum) adIdNum,-- 计划数  ").append("\n");
		sql.append(" 	SUM(usrnameNums) userNum-- 新增设备注册数 ").append("\n");
		sql.append(" FROM ( ").append("\n");
		sql.append("  ").append(this.getPlanReportData(record)).append("\n");
		sql.append(" ) t ").append("\n");
		return sql.toString();
	}

	private String getPlanReportData(AdPlanAnalyseReq record){
		StringBuffer sql = new StringBuffer();
		sql.append("         SELECT  ").append("\n");
		sql.append("                 adid, ").append("\n");
		sql.append("                 adIdNum,-- 计划数  ").append("\n");
		sql.append("                 rudeCost, --原始消耗 ").append("\n");
		sql.append("                 cost, --返点后消耗 ").append("\n");
		sql.append("                 showNums, --展示数 ").append("\n");
		sql.append("                 clickNums, --点击数 ").append("\n");
		sql.append("                 uuidNums,--新增设备 ").append("\n");
		sql.append("                 usrnameNums,-- 新增设备注册数 ").append("\n");
		sql.append("                 usrpaynameNums,--新增设备付费数 ").append("\n");
		sql.append("                 totalPayfeeNums, --累计充值人数 ").append("\n");
		sql.append("                 round(if(cost > 0, divide(toFloat64(day1DevicesharFee) * 100.00, cost), 0), 2) roi1, -- 1日ROI ").append("\n");
		sql.append("                 round(if(cost > 0, divide(toFloat64(day3DevicesharFee) * 100.00, cost), 0), 2) roi3, -- 3日ROI ").append("\n");
		sql.append("                 round(if(cost > 0, divide(toFloat64(day7DevicesharFee) * 100.00, cost), 0), 2) roi7, -- 7日ROI ").append("\n");
		sql.append("                 round(if(cost > 0, divide(toFloat64(day15DevicesharFee) * 100.00, cost), 0), 2) roi15, -- 15日ROI ").append("\n");
		sql.append("                 round(if(cost > 0, divide(toFloat64(day30DevicesharFee) * 100.00, cost), 0), 2) roi30, -- 30日ROI ").append("\n");
		sql.append("                 round(IF(toInt64(usrnameNums) > 0, divide(toFloat64(cost), usrnameNums), 0), 2) AS regCose, -- 注册成本 ").append("\n");
		sql.append("                 round(IF(toInt64(usrpaynameNums) > 0, divide(toFloat64(cost), usrpaynameNums), 0), 2) AS firstPayCose,  -- 首日付费成本 ").append("\n");
		sql.append("                 round(IF(toInt64(totalPayfeeNums) > 0, divide(toFloat64(cost), totalPayfeeNums), 0), 2) AS totalPayCose -- 累计付费成本 ").append("\n");
		sql.append("         FROM ( ").append("\n");
		sql.append("             SELECT ").append("\n");
		sql.append("                 adid,  ").append("\n");
		sql.append("                 COALESCE(COUNT(IF(reg.latest_username != '', reg.latest_username, NULL)), 0) usrnameNums,-- 新增设备注册数 ").append("\n");
		sql.append("                 COUNT(distinct reg.kid) uuidNums, --新增设备 ").append("\n");
		sql.append("                 COALESCE(SUM(IF(reg.fee_1 > 0 OR reg.givemoney_1 > 0, 1, 0)), 0) usrpaynameNums, --新增设备付费数 ").append("\n");
		sql.append("                 COALESCE(SUM(IF(reg.fee_total > 0 OR reg.givemoney_total > 0, 1, 0)), 0) totalPayfeeNums, --累计充值人数 ").append("\n");
		sql.append("                 COALESCE(SUM(reg.fee_1 * sharing), 0) day1DevicesharFee, --新增充值实付金额（分成后） ").append("\n");
		sql.append("                 COALESCE(SUM(reg.fee_3 * sharing), 0) day3DevicesharFee, --3日充值实付金额（分成后） ").append("\n");
		sql.append("                 COALESCE(SUM(reg.fee_7 * sharing), 0) day7DevicesharFee, --7日充值实付金额（分成后） ").append("\n");
		sql.append("                 COALESCE(SUM(reg.fee_15 * sharing), 0) day15DevicesharFee, --15日充值实付金额（分成后） ").append("\n");
		sql.append("                 COALESCE(SUM(reg.fee_30 * sharing), 0) day30DevicesharFee --30日充值实付金额（分成后） ").append("\n");
		sql.append("             FROM ").append("\n");
		sql.append("                 ( ").append("\n");
		sql.append("                     SELECT ").append("\n");
		sql.append("                         reg_day day, week, month, year, kid, collect, game_main pgid, os, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ad_id adid, ad_account advertiserid, convert_name convertName, ").append("\n");
		sql.append("                         deep_convert deepConvert, latest_username, is_2_retention, fee_1, givemoney_1,fee_3, fee_7, fee_15, fee_30, fee_total, givemoney_total ").append("\n");
		sql.append("                     FROM ").append("\n");
		sql.append("                         ").append(prop.getNinetydeviceregtable()).append(" reg ").append("\n");
		sql.append("                     WHERE ").append("\n");
		sql.append("                         spread_type = 1 AND adid <> '' ").append("\n");
		// 2-计划用户  ，必须有注册用户
		/*if (2 == record.getType()){
			sql.append("                         AND latest_username <> '' ").append("\n");
		}*/
		sql.append("                         AND reg_day >= ").append(record.getStartTime()).append(" AND reg_day <= ").append(record.getEndTime()).append("\n");
		if (StringUtils.isNotBlank(record.getParentchlArr())) {
			sql.append("                         AND chl_main IN ('").append(record.getParentchlArr().replaceAll(",", "','")).append("')\n");
		}
		sql.append("                         ").append(this.getWhereSql(record)).append("\n");
		sql.append("                 ) reg ").append("\n");
		sql.append("                 LEFT JOIN (SELECT CAST(id AS Int16) AS id, (1 - COALESCE(sharing, 0)) sharing FROM dim_200_pangu_mysql_parent_game pg_tmp) pg ON reg.pgid = pg.id ").append("\n");
		sql.append("             WHERE ").append("\n");
		sql.append("                 1 = 1 ").append("\n");
		sql.append("             GROUP BY ").append("\n");
		sql.append("                 adid ").append("\n");
		sql.append("         ) a ").append("\n");
		sql.append("         FULL JOIN ( ").append("\n");
		sql.append("             SELECT ").append("\n");
		sql.append("                 adid, ").append("\n");
		sql.append("                 COUNT(DISTINCT adid) adIdNum,-- 计划数  ").append("\n");
		sql.append("                 toFloat64(COALESCE(SUM(rudeCost), 0)) rudeCost, --原始消耗 ").append("\n");
		sql.append("                 toFloat64(COALESCE(SUM(cost), 0)) cost, --返点后消耗 ").append("\n");
		sql.append("                 COALESCE(SUM(shownums), 0) showNums, --展示数 ").append("\n");
		sql.append("                 COALESCE(SUM(clicknums), 0) clickNums --点击数 ").append("\n");
		sql.append("             FROM ").append("\n");
		sql.append("                 ( ").append("\n");
		sql.append("                     SELECT ").append("\n");
		sql.append("                         day, week, month, year, collect, ad_show shownums, click clicknums, ad_id adid, ad_account advertiserid, convert_name convertName, deep_convert deepConvert, COALESCE(rude_cost, 0) rudeCost, COALESCE(cost,0) cost ").append("\n");
		sql.append("                     FROM ").append("\n");
		sql.append("                         ").append(prop.getAdidrebatetable()).append(" ard ").append("\n");
		sql.append("                     WHERE ").append("\n");
		sql.append("                         day >= ").append(record.getStartTime()).append(" AND day <= ").append(record.getEndTime()).append("\n");
		sql.append("                         ").append(this.getWhereSql(record)).append("\n");
		sql.append("                 ) ard ").append("\n");
		sql.append("                 LEFT JOIN (SELECT os, game_main pgid, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ad_id FROM ").append(prop.getAdptypetable()).append(") ap ON ard.adid = ap.ad_id ").append("\n");
		sql.append("             WHERE 1 = 1 ").append("\n");
		if (StringUtils.isNotBlank(record.getParentchlArr())) {
			sql.append("                 AND parentchl IN ('").append(record.getParentchlArr().replaceAll(",", "','")).append("')\n");
		}
		sql.append("             GROUP BY ").append("\n");
		sql.append("                 adid ").append("\n");
		sql.append("         ) b USING (adid) ").append("\n");
		sql.append("         WHERE adid <> '' AND cost > 0 ").append("\n");
		// 2-计划用户  ，必须有注册用户
		/*if (2 == record.getType()){
			sql.append("         AND usrnameNums > 0 ").append("\n");
		}*/
		sql.append("         ").append(this.getCostWhereSql(record)).append("\n");
		return sql.toString();
	}

	private String getWhereSql(AdPlanAnalyseReq record) {
		final String adAccount = record.getAdAccount();
		final String adId = record.getAdId();
		final StringBuilder builder = new StringBuilder();

		if (StringUtils.isNotBlank(adAccount)){
			builder.append(" AND ad_account IN ('").append(adAccount.replaceAll(",", "','")).append("') \n");
		}
		if (StringUtils.isNotBlank(adId)){
			builder.append(" AND ad_id IN ('").append(adId.replaceAll(",", "','")).append("') \n");
		}
		return builder.toString();
	}

	private String getCostWhereSql(AdPlanAnalyseReq record) {
		final String firstRoiMin = record.getFirstRoiMin();
		final String firstRoiMax = record.getFirstRoiMax();
		final String threeRoiMin = record.getThreeRoiMin();
		final String threeRoiMax = record.getThreeRoiMax();
		final String sevenRoiMin = record.getSevenRoiMin();
		final String sevenRoiMax = record.getSevenRoiMax();
		final String fifteenRoiMin = record.getFifteenRoiMin();
		final String fifteenRoiMax = record.getFifteenRoiMax();
		final String thirtyRoiMin = record.getThirtyRoiMin();
		final String thirtyRoiMax = record.getThirtyRoiMax();
		final String regCostMin = record.getRegCostMin();
		final String regCostMax = record.getRegCostMax();
		final String firstPayCostMin = record.getFirstPayCostMin();
		final String firstPayCostMax = record.getFirstPayCostMax();
		final String totalPayCostMin = record.getTotalPayCostMin();
		final String totalPayCostMax = record.getTotalPayCostMax();

		final StringBuilder builder = new StringBuilder();
		if (StringUtils.isNotBlank(firstRoiMin)){
			builder.append(" AND roi1 >= ").append(firstRoiMin).append(" \n");
		}
		if (StringUtils.isNotBlank(firstRoiMax)){
			builder.append(" AND roi1 <= ").append(firstRoiMax).append(" \n");
		}
		if (StringUtils.isNotBlank(threeRoiMin)){
			builder.append(" AND roi3 >= ").append(threeRoiMin).append(" \n");
		}
		if (StringUtils.isNotBlank(threeRoiMax)){
			builder.append(" AND roi3 <= ").append(threeRoiMax).append(" \n");
		}
		if (StringUtils.isNotBlank(sevenRoiMin)){
			builder.append(" AND roi7 >= ").append(sevenRoiMin).append(" \n");
		}
		if (StringUtils.isNotBlank(sevenRoiMax)){
			builder.append(" AND roi7 <= ").append(sevenRoiMax).append(" \n");
		}
		if (StringUtils.isNotBlank(fifteenRoiMin)){
			builder.append(" AND roi15 >= ").append(fifteenRoiMin).append(" \n");
		}
		if (StringUtils.isNotBlank(fifteenRoiMax)){
			builder.append(" AND roi15 <= ").append(fifteenRoiMax).append(" \n");
		}
		if (StringUtils.isNotBlank(thirtyRoiMin)){
			builder.append(" AND roi30 >= ").append(thirtyRoiMin).append(" \n");
		}
		if (StringUtils.isNotBlank(thirtyRoiMax)){
			builder.append(" AND roi30 <= ").append(thirtyRoiMax).append(" \n");
		}
		if (StringUtils.isNotBlank(regCostMin)){
			builder.append(" AND regCose >= ").append(regCostMin).append(" \n");
		}
		if (StringUtils.isNotBlank(regCostMax)){
			builder.append(" AND regCose <= ").append(regCostMax).append(" \n");
		}
		if (StringUtils.isNotBlank(firstPayCostMin)){
			builder.append(" AND firstPayCose >= ").append(firstPayCostMin).append(" \n");
		}
		if (StringUtils.isNotBlank(firstPayCostMax)){
			builder.append(" AND firstPayCose <= ").append(firstPayCostMax).append(" \n");
		}
		if (StringUtils.isNotBlank(totalPayCostMin)){
			builder.append(" AND totalPayCose >= ").append(totalPayCostMin).append(" \n");
		}
		if (StringUtils.isNotBlank(totalPayCostMax)){
			builder.append(" AND totalPayCose <= ").append(totalPayCostMax).append(" \n");
		}
		return builder.toString();
	}
}
